﻿/*
 * 由SharpDevelop创建。
 * 用户： rong
 * 日期: 2015-01-18
 * 时间: 17:32
 * 
 * 要改变这种模板请点击 工具|选项|代码编写|编辑标准头文件
 */




using System;

using System.Collections;

using System.Collections.Specialized;

using System.Data;

using System.Data.SQLite;//这个可以去网上下载

//using System.Configuration;
namespace sqlliteop
{



	public class SQLiteHelper

	{

		

		public static string connectionString = "Data Source="+System.Environment.CurrentDirectory + @"/data/"+"commad.db3";

		


		public SQLiteHelper() { }



		#region 公用方法



		public static int GetMaxID(string FieldName, string TableName)

		{

			string strsql = "select max(" + FieldName + ")+1 from " + TableName;

			object obj = GetSingle(strsql);

			if (obj == null)

			{

				return 1;

			}

			else

			{

				return int.Parse(obj.ToString());

			}

		}



		public static bool Exists(string strSql)

		{

			object obj = GetSingle(strSql);

			int cmdresult;

			if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

			{

				cmdresult = 0;

			}

			else

			{

				cmdresult = int.Parse(obj.ToString());

			}

			if (cmdresult == 0)

			{

				return false;

			}

			else

			{

				return true;

			}

		}



		public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)

		{

			object obj = GetSingle(strSql, cmdParms);

			int cmdresult;

			if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

			{

				cmdresult = 0;

			}

			else

			{

				cmdresult = int.Parse(obj.ToString());

			}

			if (cmdresult == 0)

			{

				return false;

			}

			else

			{

				return true;

			}

		}



		#endregion
		/// <summary>
		/// 创建表
		/// </summary>
		/// <param name="tableName"></param>
		public static void creatTabel(string tableName)
		{
			//判断表是否存在
			string sql = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='"+tableName+"'";

			SQLiteConnection conn = new SQLiteConnection(connectionString);   //connectionString
			SQLiteCommand cmd = new SQLiteCommand(sql, conn);
			conn.Open();
			int recordCount = (int)(Int64)cmd.ExecuteScalar();   //如果存在返回1，不存在返回0

			if (recordCount==0)
			{
				sql = "create table [" + tableName + "](_id integer primary key autoincrement,typeID int,runtime timestamp,cmd varchar(2000))";
				cmd.CommandText = sql;
				cmd.Connection = conn;
				cmd.ExecuteNonQuery();
			}
			conn.Close();
		}

		#region  执行简单SQL语句



		/// <summary>

		/// 执行SQL语句，返回影响的记录数

		/// </summary>

		/// <param name="SQLString">SQL语句</param>

		/// <returns>影响的记录数</returns>

		public static int ExecuteSql(string SQLString)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))

				{

					try

					{

						connection.Open();

						int rows = cmd.ExecuteNonQuery();

						return rows;

					}

					catch (System.Data.SQLite.SQLiteException E)

					{

						connection.Close();

						throw new Exception(E.Message);

					}

				}

			}

		}



		/// <summary>

		/// 执行SQL语句，设置命令的执行等待时间

		/// </summary>

		/// <param name="SQLString"></param>

		/// <param name="Times"></param>

		/// <returns></returns>

		public static int ExecuteSqlByTime(string SQLString, int Times)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))

				{

					try

					{

						connection.Open();

						cmd.CommandTimeout = Times;

						int rows = cmd.ExecuteNonQuery();

						return rows;

					}

					catch (System.Data.SQLite.SQLiteException E)

					{

						connection.Close();

						throw new Exception(E.Message);

					}

				}

			}

		}



		/// <summary>

		/// 执行多条SQL语句，实现数据库事务。

		/// </summary>

		/// <param name="SQLStringList">多条SQL语句</param>

		public static void ExecuteSqlTran(ArrayList SQLStringList)

		{

			using (SQLiteConnection conn = new SQLiteConnection(connectionString))

			{

				conn.Open();

				SQLiteCommand cmd = new SQLiteCommand();

				cmd.Connection = conn;

				SQLiteTransaction tx = conn.BeginTransaction();

				cmd.Transaction = tx;

				try

				{

					for (int n = 0; n < SQLStringList.Count; n++)

					{

						string strsql = SQLStringList[n].ToString();

						if (strsql.Trim().Length > 1)

						{

							cmd.CommandText = strsql;

							cmd.ExecuteNonQuery();

						}

					}

					tx.Commit();

				}

				catch (System.Data.SQLite.SQLiteException E)

				{

					tx.Rollback();

					throw new Exception(E.Message);

				}

			}

		}



		/// <summary>

		/// 执行带一个存储过程参数的的SQL语句。

		/// </summary>

		/// <param name="SQLString">SQL语句</param>

		/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>

		/// <returns>影响的记录数</returns>

		public static int ExecuteSql(string SQLString, string content)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);

				SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);

				myParameter.Value = content;

				cmd.Parameters.Add(myParameter);

				try

				{

					connection.Open();

					int rows = cmd.ExecuteNonQuery();

					return rows;

				}

				catch (System.Data.SQLite.SQLiteException E)

				{

					throw new Exception(E.Message);

				}

				finally

				{

					cmd.Dispose();

					connection.Close();

				}

			}

		}



		/// <summary>

		/// 执行带一个存储过程参数的的SQL语句。

		/// </summary>

		/// <param name="SQLString">SQL语句</param>

		/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>

		/// <returns>影响的记录数</returns>

		public static object ExecuteSqlGet(string SQLString, string content)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);

				SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);

				myParameter.Value = content;

				cmd.Parameters.Add(myParameter);

				try

				{

					connection.Open();

					object obj = cmd.ExecuteScalar();

					if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

					{

						return null;

					}

					else

					{

						return obj;

					}

				}

				catch (System.Data.SQLite.SQLiteException E)

				{

					throw new Exception(E.Message);

				}

				finally

				{

					cmd.Dispose();

					connection.Close();

				}

			}

		}



		/// <summary>

		/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)

		/// </summary>

		/// <param name="strSQL">SQL语句</param>

		/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>

		/// <returns>影响的记录数</returns>

		public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);

				SQLiteParameter myParameter = new SQLiteParameter("@fs", DbType.Binary);

				myParameter.Value = fs;

				cmd.Parameters.Add(myParameter);

				try

				{

					connection.Open();

					int rows = cmd.ExecuteNonQuery();

					return rows;

				}

				catch (System.Data.SQLite.SQLiteException E)

				{

					throw new Exception(E.Message);

				}

				finally

				{

					cmd.Dispose();

					connection.Close();

				}

			}

		}



		/// <summary>

		/// 执行一条计算查询结果语句，返回查询结果（object）。

		/// </summary>

		/// <param name="SQLString">计算查询结果语句</param>

		/// <returns>查询结果（object）</returns>

		public static object GetSingle(string SQLString)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))

				{

					try

					{

						connection.Open();

						object obj = cmd.ExecuteScalar();

						if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

						{

							return null;

						}

						else

						{

							return obj;

						}

					}

					catch (System.Data.SQLite.SQLiteException e)

					{

						connection.Close();

						throw new Exception(e.Message);

					}

				}

			}

		}



		/// <summary>

		/// 执行查询语句，返回SQLiteDataReader(使用该方法切记要手工关闭SQLiteDataReader和连接)

		/// </summary>

		/// <param name="strSQL">查询语句</param>

		/// <returns>SQLiteDataReader</returns>

		public static SQLiteDataReader ExecuteReader(string strSQL)

		{

			SQLiteConnection connection = new SQLiteConnection(connectionString);

			SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);

			try

			{

				connection.Open();

				SQLiteDataReader myReader = cmd.ExecuteReader();

				return myReader;

			}

			catch (System.Data.SQLite.SQLiteException e)

			{

				throw new Exception(e.Message);

			}

			//finally //不能在此关闭，否则，返回的对象将无法使用

			//{

			//    cmd.Dispose();

			//    connection.Close();

			//}

		}



		/// <summary>

		/// 执行查询语句，返回DataSet

		/// </summary>

		/// <param name="SQLString">查询语句</param>

		/// <returns>DataSet</returns>

		public static DataSet Query(string SQLString)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				DataSet ds = new DataSet();

				try

				{

					connection.Open();

					SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);

					command.Fill(ds, "ds");

				}

				catch (System.Data.SQLite.SQLiteException ex)

				{

					throw new Exception(ex.Message);

				}

				return ds;

			}

		}



		public static DataSet Query(string SQLString, string TableName)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				DataSet ds = new DataSet();

				try

				{

					connection.Open();

					SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);

					command.Fill(ds, TableName);

				}

				catch (System.Data.SQLite.SQLiteException ex)

				{

					throw new Exception(ex.Message);

				}

				return ds;

			}

		}



		/// <summary>

		/// 执行查询语句，返回DataSet,设置命令的执行等待时间

		/// </summary>

		/// <param name="SQLString"></param>

		/// <param name="Times"></param>

		/// <returns></returns>

		public static DataSet Query(string SQLString, int Times)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				DataSet ds = new DataSet();

				try

				{

					connection.Open();

					SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);

					command.SelectCommand.CommandTimeout = Times;

					command.Fill(ds, "ds");

				}

				catch (System.Data.SQLite.SQLiteException ex)

				{

					throw new Exception(ex.Message);

				}

				return ds;

			}

		}



		#endregion



		#region 执行带参数的SQL语句



		/// <summary>

		/// 执行SQL语句，返回影响的记录数

		/// </summary>

		/// <param name="SQLString">SQL语句</param>

		/// <returns>影响的记录数</returns>

		public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				using (SQLiteCommand cmd = new SQLiteCommand())

				{

					try

					{

						PrepareCommand(cmd, connection, null, SQLString, cmdParms);

						int rows = cmd.ExecuteNonQuery();

						cmd.Parameters.Clear();

						return rows;

					}

					catch (System.Data.SQLite.SQLiteException E)

					{

						throw new Exception(E.Message);

					}

				}

			}

		}



		/// <summary>

		/// 执行多条SQL语句，实现数据库事务。

		/// </summary>

		/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>

		public static void ExecuteSqlTran(Hashtable SQLStringList)

		{

			using (SQLiteConnection conn = new SQLiteConnection(connectionString))

			{

				conn.Open();

				using (SQLiteTransaction trans = conn.BeginTransaction())

				{

					SQLiteCommand cmd = new SQLiteCommand();

					try

					{

						//循环

						foreach (DictionaryEntry myDE in SQLStringList)

						{

							string cmdText = myDE.Key.ToString();

							SQLiteParameter[] cmdParms = (SQLiteParameter[]) myDE.Value;

							PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

							int val = cmd.ExecuteNonQuery();

							cmd.Parameters.Clear();



							trans.Commit();

						}

					}

					catch

					{

						trans.Rollback();

						throw;

					}

				}

			}

		}



		/// <summary>

		/// 执行一条计算查询结果语句，返回查询结果（object）。

		/// </summary>

		/// <param name="SQLString">计算查询结果语句</param>

		/// <returns>查询结果（object）</returns>

		public static object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				using (SQLiteCommand cmd = new SQLiteCommand())

				{

					try

					{

						PrepareCommand(cmd, connection, null, SQLString, cmdParms);

						object obj = cmd.ExecuteScalar();

						cmd.Parameters.Clear();

						if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

						{

							return null;

						}

						else

						{

							return obj;

						}

					}

					catch (System.Data.SQLite.SQLiteException e)

					{

						throw new Exception(e.Message);

					}

				}

			}

		}



		/// <summary>

		/// 执行查询语句，返回SQLiteDataReader (使用该方法切记要手工关闭SQLiteDataReader和连接)

		/// </summary>

		/// <param name="strSQL">查询语句</param>

		/// <returns>SQLiteDataReader</returns>

		public static SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)

		{

			SQLiteConnection connection = new SQLiteConnection(connectionString);

			SQLiteCommand cmd = new SQLiteCommand();

			try

			{

				PrepareCommand(cmd, connection, null, SQLString, cmdParms);

				SQLiteDataReader myReader = cmd.ExecuteReader();

				cmd.Parameters.Clear();

				return myReader;

			}

			catch (System.Data.SQLite.SQLiteException e)

			{

				throw new Exception(e.Message);

			}

			//finally //不能在此关闭，否则，返回的对象将无法使用

			//{

			//    cmd.Dispose();

			//    connection.Close();

			//}



		}



		/// <summary>

		/// 执行查询语句，返回DataSet

		/// </summary>

		/// <param name="SQLString">查询语句</param>

		/// <returns>DataSet</returns>

		public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)

		{

			using (SQLiteConnection connection = new SQLiteConnection(connectionString))

			{

				SQLiteCommand cmd = new SQLiteCommand();

				PrepareCommand(cmd, connection, null, SQLString, cmdParms);

				using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))

				{

					DataSet ds = new DataSet();

					try

					{

						da.Fill(ds, "ds");

						cmd.Parameters.Clear();

					}

					catch (System.Data.SQLite.SQLiteException ex)

					{

						throw new Exception(ex.Message);

					}

					return ds;

				}

			}

		}



		public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn,

		                                  SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)

		{

			if (conn.State != ConnectionState.Open)

				conn.Open();

			cmd.Connection = conn;

			cmd.CommandText = cmdText;

			if (trans != null)

				cmd.Transaction = trans;

			cmd.CommandType = CommandType.Text;//cmdType;

			if (cmdParms != null)

			{





				foreach (SQLiteParameter parameter in cmdParms)

				{

					if ((parameter.Direction == ParameterDirection.InputOutput

					     || parameter.Direction == ParameterDirection.Input) &&

					    (parameter.Value == null))

					{

						parameter.Value = DBNull.Value;

					}

					cmd.Parameters.Add(parameter);

				}

			}

		}



		#endregion



		#region 参数转换

		/// <summary>

		/// 放回一个SQLiteParameter

		/// </summary>

		/// <param name="name">参数名字</param>

		/// <param name="type">参数类型</param>

		/// <param name="size">参数大小</param>

		/// <param name="value">参数值</param>

		/// <returns>SQLiteParameter的值</returns>

		public static SQLiteParameter MakeSQLiteParameter(string name,

		                                                  DbType type, int size, object value)

		{

			SQLiteParameter parm = new SQLiteParameter(name, type, size);

			parm.Value = value;

			return parm;

		}



		public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, object value)

		{

			SQLiteParameter parm = new SQLiteParameter(name, type);

			parm.Value = value;

			return parm;

		}



		#endregion
	}

}





